using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Text;
using Developex.DatabaseWrapper.Secure;

namespace Developex.DatabaseWrapper
{
	/// <summary>
	/// Connection helper class, currently use sql client only
	/// </summary>
	public class Connection : IDisposable, IDatabaseErrorHandler
	{
		///<summary>
		///</summary>
		public readonly ProviderType ProviderType;
		///<summary>
		///</summary>
		public const char FieldNameDelimiter = ' ';

		private readonly DbConnection conn;
		private DbTransaction transaction;
		private IDatabaseEventHandler eventHandler;
		private readonly IDatabaseErrorHandler errorHandler;
		private DatabaseException _LastDatabaseException;

		#region Constructors

		/// <summary>
		/// Create and open db conn
		/// </summary>
		/// <param name="connectionString"></param>
		public Connection(string connectionString)
			: this(ProviderType.MSSQL, connectionString)
		{
		}


		/// <summary>
		/// Create and open db conn
		/// </summary>
		/// <param name="providerType"></param>
		/// <param name="connectionString"></param>
		public Connection(ProviderType providerType, string connectionString)
			: this(providerType, connectionString, new DefaultDatabaseEventHandler())
		{
		}

		/// <summary>
		/// Create and open db conn, init custom event handler
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="eventHandler"></param>
		public Connection(string connectionString, IDatabaseEventHandler eventHandler)
			: this(ProviderType.MSSQL, connectionString, eventHandler)
		{
		}

		/// <summary>
		/// Create and open db conn, init custom event handler
		/// </summary>
		/// <param name="providerType"></param>
		/// <param name="connectionString"></param>
		/// <param name="eventHandler"></param>
		public Connection(ProviderType providerType, string connectionString, IDatabaseEventHandler eventHandler)
		{
			this.ProviderType = providerType;

			if (string.IsNullOrEmpty(connectionString))
				throw new InvalidOperationException("DatabaseWrapper: Connection string is not initialized");

			this.eventHandler = eventHandler;
			if (eventHandler == null)
				this.eventHandler = new DefaultDatabaseEventHandler();

			if (this.eventHandler is IDatabaseErrorHandler)
				errorHandler = (IDatabaseErrorHandler)this.eventHandler;

			conn = CreateConnection();
			conn.ConnectionString = connectionString;
			conn.Open();
		}

		/// <summary>
		/// Create and open db conn, init custom event handler
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="eventHandler"></param>
		/// <param name="errorHandler"></param>
		public Connection(string connectionString, IDatabaseEventHandler eventHandler, IDatabaseErrorHandler errorHandler)
			: this(ProviderType.MSSQL, connectionString, eventHandler, errorHandler)
		{
		}

		/// <summary>
		/// Create and open db conn, init custom event handler
		/// </summary>
		/// <param name="providerType"></param>
		/// <param name="connectionString"></param>
		/// <param name="eventHandler"></param>
		/// <param name="errorHandler"></param>
		public Connection(ProviderType providerType, string connectionString, IDatabaseEventHandler eventHandler, IDatabaseErrorHandler errorHandler)
		{
			this.ProviderType = providerType;

			if (string.IsNullOrEmpty(connectionString))
				throw new InvalidOperationException("DatabaseWrapper: Connection string is not initialized");

			this.eventHandler = eventHandler;
			if (eventHandler == null)
				this.eventHandler = new DefaultDatabaseEventHandler();

			this.errorHandler = errorHandler;

			conn = CreateConnection();
			conn.ConnectionString = connectionString;
			conn.Open();
		}

		#endregion

		#region Event handling

		/// <summary>
		/// Get/set database event handler
		/// </summary>
		public IDatabaseEventHandler EventHandler
		{
			get { return eventHandler; }
			set { eventHandler = value; }
		}

		/// <summary>
		/// Get last exception
		/// </summary>
		/// <returns></returns>
		public DatabaseException LastException
		{
			get { return _LastDatabaseException; }
		}

		/// <summary>
		/// Supress error handling
		/// </summary>
		public bool SupressErrorHandling = false;

		/// <summary>
		/// Process database exception
		/// </summary>
		/// <param name="exception"></param>
		public void OnDatabaseException(DatabaseException exception)
		{
			_LastDatabaseException = exception;
			if (!SupressErrorHandling && errorHandler != null)
				errorHandler.OnDatabaseException(exception);
		}

		/// <summary>
		/// Process database warning
		/// </summary>
		/// <param name="warning"></param>
		public void OnDatabaseWarning(DatabaseWarning warning)
		{
			if (!SupressErrorHandling && errorHandler != null)
				errorHandler.OnDatabaseWarning(warning);
		}

		#endregion

		#region Disposing

		/// <summary>
		/// Close conn. The same as Dispose().
		/// </summary>
		public void Close()
		{
			Dispose();
		}

		/// <summary>
		/// Dispose conn
		/// </summary>
		public void Dispose()
		{
			// don't call destructor
			GC.SuppressFinalize(this);
			Disposing(false);
		}

		/// <summary>
		/// Destructor
		/// </summary>
		~Connection()
		{
			Disposing(true);
		}

		/// <summary>
		/// Dispose conn
		/// </summary>
		/// <param name="fromDestructor"></param>
		protected virtual void Disposing(bool fromDestructor)
		{
			//#if DEBUG
			//            if (fromDestructor)
			//                throw new InvalidOperationException("Developex.DatabaseWrapper.Connection.Disposing: Disposing was called in destructor!");
			//#endif

			conn.Close();
		}

		#endregion

		#region Query functions

		#region ExecuteNonQuery

		/// <summary>
		/// Execute non query command
		/// </summary>
		/// <param name="qb"></param>
        /// <returns>The number of rows affected</returns>
		public int ExecuteNonQuery(QueryBuilder qb)
		{
			return GetCommand(qb).ExecuteNonQuery();
		}

		/// <summary>
		/// Execute text non query command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>The number of rows affected</returns>
		public int ExecuteNonQuery(string text, params IDataParameter[] parameters)
		{
			return GetCommand(text, parameters).ExecuteNonQuery();
		}

		/// <summary>
		/// Execute non query command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>The number of rows affected</returns>
		public int ExecuteNonQuery(string text, CommandType type, params IDataParameter[] parameters)
		{
			return GetCommand(text, type, parameters).ExecuteNonQuery();
		}

		#endregion

		#region ExecuteDataReader

		/// <summary>
		/// Execute query command
		/// </summary>
		/// <param name="qb"></param>
		/// <returns></returns>
		public IDataReader ExecuteDataReader(QueryBuilder qb)
		{
			return GetCommand(qb).ExecuteReader();
		}

		/// <summary>
		/// Execute text query command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Data reader</returns>
		public IDataReader ExecuteDataReader(string text, params IDataParameter[] parameters)
		{
			return GetCommand(text, CommandType.Text, parameters).ExecuteReader();
		}

		/// <summary>
		/// Execute query command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Data reader</returns>
		public IDataReader ExecuteDataReader(string text, CommandType type, params IDataParameter[] parameters)
		{
			return GetCommand(text, type, parameters).ExecuteReader();
		}

		#endregion

		#region ExecuteScalar

		/// <summary>
		/// Execute scalar command
		/// </summary>
		/// <param name="qb"></param>
		/// <returns></returns>
		public object ExecuteScalar(QueryBuilder qb)
		{
			object ret = GetCommand(qb).ExecuteScalar();
			return ret == DBNull.Value ? null : ret;
		}

		/// <summary>
		/// Execute text scalar command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Command result value</returns>
		public object ExecuteScalar(string text, params IDataParameter[] parameters)
		{
			object ret = GetCommand(text, CommandType.Text, parameters).ExecuteScalar();
			return ret == DBNull.Value ? null : ret;
		}

		/// <summary>
		/// Execute scalar command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Command result value</returns>
		public object ExecuteScalar(string text, CommandType type, params IDataParameter[] parameters)
		{
			object ret = GetCommand(text, type, parameters).ExecuteScalar();
			return ret == DBNull.Value ? null : ret;
		}

		#endregion

		#region ExecuteDataSet

		/// <summary>
		/// Execute dataset command
		/// </summary>
		/// <param name="qb"></param>
		/// <returns></returns>
		public DataSet ExecuteDataSet(QueryBuilder qb)
		{
			return ExecuteDataSet(qb.ToString(), CommandType.Text, qb.Parameters.ToArray());
		}

		/// <summary>
		/// Execute dataset command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Result dataset</returns>
		public DataSet ExecuteDataSet(string text, params IDataParameter[] parameters)
		{
			return ExecuteDataSet(text, CommandType.Text, parameters);
		}

		/// <summary>
		/// Execute dataset command
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Result dataset</returns>
		public DataSet ExecuteDataSet(string text, CommandType type, params IDataParameter[] parameters)
		{
			DbCommand command = GetCommand(text, type, parameters);
			using (DbDataAdapter adapter = CreateDataAdapter(command))
			{
				DataSet ds = new DataSet();
				adapter.Fill(ds);
				command.Parameters.Clear();
				return ds;
			}
		}

		#endregion

		#region ExecuteScalarlist for large items count

		/// <summary>
		/// Execute scalar list for large items count
		/// </summary>
		/// <param name="qb"></param>
		/// <param name="pageSize"></param>
		/// <param name="primaryKeyFields"></param>
		/// <returns></returns>
		public List<T> ExecuteScalarList_Large<T>(QueryBuilder qb, int pageSize, IField[] primaryKeyFields) where T : class
		{
			int itemsCount;
			int pageIndex = 1;
			List<IDataParameter> parameters = qb.CopyParameters(this);
			qb.PrepareForSelectQuery(this, pageIndex, pageSize, out itemsCount, primaryKeyFields);
			if (itemsCount == 0)
				return new List<T>();
			List<T> items = new List<T>(itemsCount);
			int itemsLoaded = 0;
			while (true)
			{
				List<T> pageItems = ExecuteScalarList<T>(qb);
				itemsLoaded += pageItems.Count;
				if (itemsLoaded == itemsCount)
					break;

				if (itemsLoaded > itemsCount || pageItems.Count != pageSize)
					throw new DatabaseException("List of items was changed", null, "ExecuteScalarList_Large", GetType());

				qb.Parameters = parameters;
				parameters = qb.CopyParameters(this);
				pageIndex++;
				int checkItemsCount;
				qb.PrepareForSelectQuery(this, pageIndex, pageSize, out checkItemsCount, primaryKeyFields);

				if (checkItemsCount != itemsCount)
					throw new DatabaseException("List of items was changed", null, "ExecuteScalarList_Large", GetType());
			}
			return items;
		}

		#endregion

		#region ExecuteScalarList

		/// <summary>
		/// Execute scalar list
		/// </summary>
		/// <param name="qb"></param>
		/// <returns></returns>
		/// <remarks>for value types DBNulls will be skipped</remarks>
		public List<T> ExecuteScalarList<T>(QueryBuilder qb)
		{
			using (IDataReader reader = GetCommand(qb).ExecuteReader())
			{
				return HelperFunctions.ReadAsList<T>(reader);
			}
		}

		/// <summary>
		/// Execute scalar list
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns></returns>
		/// <remarks>for value types DBNulls will be skipped</remarks>
		public List<T> ExecuteScalarList<T>(string text, params IDataParameter[] parameters)
		{
			using (IDataReader reader = GetCommand(text, parameters).ExecuteReader())
			{
				return HelperFunctions.ReadAsList<T>(reader);
			}
		}

		/// <summary>
		/// Execute scalar list
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns></returns>
		/// <remarks>for value types DBNulls will be skipped</remarks>
		public List<T> ExecuteScalarList<T>(string text, CommandType type, params IDataParameter[] parameters)
		{
			using (IDataReader reader = GetCommand(text, type, parameters).ExecuteReader())
			{
				return HelperFunctions.ReadAsList<T>(reader);
			}
		}

		#endregion

		#region ExecuteScalarListWithoutNulls

		/// <summary>
		/// Execute scalar list(T is struct)
		/// </summary>
		/// <param name="qb"></param>
		/// <returns></returns>
		/// <remarks>for value types you can use ExecuteScalarList function (behaviour is the same)</remarks>
		public List<T> ExecuteScalarListWithoutNulls<T>(QueryBuilder qb)
		{
			using (IDataReader reader = GetCommand(qb).ExecuteReader())
			{
				return HelperFunctions.ReadAsListWithoutNulls<T>(reader);
			}
		}

		/// <summary>
		/// Execute scalar list(T is struct)
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Result dataset</returns>
		/// <remarks>for value types you can use ExecuteScalarList function (behaviour is the same)</remarks>
		public List<T> ExecuteScalarListWithoutNulls<T>(string text, params IDataParameter[] parameters)
		{
			using (IDataReader reader = GetCommand(text, parameters).ExecuteReader())
			{
				return HelperFunctions.ReadAsListWithoutNulls<T>(reader);
			}
		}

		/// <summary>
		/// Execute scalar list(T is struct)
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns>Result dataset</returns>
		/// <remarks>for value types you can use ExecuteScalarList function (behaviour is the same)</remarks>
		public List<T> ExecuteScalarListWithoutNulls<T>(string text, CommandType type, params IDataParameter[] parameters)
		{
			using (IDataReader reader = GetCommand(text, type, parameters).ExecuteReader())
			{
				return HelperFunctions.ReadAsListWithoutNulls<T>(reader);
			}
		}

		#endregion

		#region ExecuteDictionary

		/// <summary>
		/// Execute dictionary
		/// </summary>
		/// <param name="qb"></param>
		/// <returns></returns>
		/// <remarks>if TKey or TValue is value type and according value in results is DBNull then row of results will be skipped</remarks>
		public Dictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(QueryBuilder qb)
		{
			using (IDataReader reader = GetCommand(qb).ExecuteReader())
			{
				return HelperFunctions.ReadAsDictionary<TKey, TValue>(reader);
			}
		}

		/// <summary>
		/// Execute dictionary
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns></returns>
		/// <remarks>if TKey or TValue is value type and according value in results is DBNull then row of results will be skipped</remarks>
		public Dictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string text, params IDataParameter[] parameters)
		{
			using (IDataReader reader = GetCommand(text, parameters).ExecuteReader())
			{
				return HelperFunctions.ReadAsDictionary<TKey, TValue>(reader);
			}
		}

		/// <summary>
		/// Execute dictionary
		/// </summary>
		/// <param name="text">Command text</param>
		/// <param name="type">Command type</param>
		/// <param name="parameters">Command parameters</param>
		/// <returns></returns>
		/// <remarks>if TKey or TValue is value type and according value in results is DBNull then row of results will be skipped</remarks>
		public Dictionary<TKey, TValue> ExecuteDictionary<TKey, TValue>(string text, CommandType type, params IDataParameter[] parameters)
		{
			using (IDataReader reader = GetCommand(text, type, parameters).ExecuteReader())
			{
				return HelperFunctions.ReadAsDictionary<TKey, TValue>(reader);
			}
		}

		#endregion

		#endregion

		#region Transactions

		/// <summary>
		/// Begin transaction
		/// </summary>
		public void BeginTransaction()
		{
			if (InTransaction)
				OnDatabaseException(new DatabaseException("Have not closed transaction", null, "Connection.BeginTransaction()", GetType()));
			transaction = conn.BeginTransaction();
		}

		/// <summary>
		/// Begin transaction
		/// </summary>
		public void BeginTransaction(IsolationLevel isolationLevel)
		{
			if (InTransaction)
				OnDatabaseException(new DatabaseException("Have not closed transaction", null, "Connection.BeginTransaction()", GetType()));
			transaction = conn.BeginTransaction(isolationLevel);
		}

		/// <summary>
		/// Is in transaction
		/// </summary>
		public bool InTransaction
		{
			get { return transaction != null; }
		}

		/// <summary>
		/// Commit transaction
		/// </summary>
		public void Commit()
		{
			if (!InTransaction)
				OnDatabaseException(new DatabaseException("Transaction was not opened", null, "Connection.Commit()", GetType()));
			transaction.Commit();
			transaction = null;
		}

		/// <summary>
		/// Rollback transaction
		/// </summary>
		public void Rollback()
		{
			if (!InTransaction)
				OnDatabaseException(new DatabaseException("Transaction was not opened", null, "Connection.Rollback()", GetType()));
			transaction.Rollback();
			transaction = null;
		}

		#endregion

		#region Internal implementation functions

		private DbCommand GetCommand(string text, CommandType type, params IDataParameter[] parameters)
		{
			EventHandler.OnResetBetweenQueries();
			DbCommand command = CreateCommand();
			InitializeCommand(command, text, type, parameters);
			return command;
		}

		private DbCommand GetCommand(string text, params IDataParameter[] parameters)
		{
			EventHandler.OnResetBetweenQueries();
			DbCommand command = CreateCommand();
			InitializeCommand(command, text, CommandType.Text, parameters);
			return command;
		}

		private DbCommand GetCommand(QueryBuilder qb)
		{
			EventHandler.OnResetBetweenQueries();
			DbCommand command = CreateCommand();
			InitializeCommand(command, qb.ToString(), CommandType.Text, qb.Parameters);
			return command;
		}

		private void InitializeCommand(DbCommand command, string text, CommandType type, IEnumerable<IDataParameter> parameters)
		{
			command.CommandText = text;
			command.CommandType = type;
			command.Connection = conn;
			command.Transaction = transaction;
			foreach (IDataParameter parameter in parameters)
			{
				command.Parameters.Add(parameter);
			}
		}

		#endregion

		#region provider dependent functions

		#region CreateParameter - signatures

		/// <summary>
		/// Create db parameter from IField
		/// </summary>
		/// <param name="name"></param>
		/// <param name="field"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(string name, IField field, object value)
		{
			DbType? type = field.GetDbType();
			IDataParameter par;

			if (value != null && value != DBNull.Value)
			{
				if ((field.Flags & FieldFlags.Secure) != 0)
				{
					// we know for sure that this is string field because currently only string type is supported for Secure fields
					string v = (field.Flags & FieldFlags.SecuredNoExtraBytes) != 0
									? DESHelper.EncryptTextNoExtraBytes((string)value)
									: DESHelper.EncryptText((string)value);
					par = CreateParameter(name, v);
				}
				else if ((field.Flags & FieldFlags.CustomEncrypt) != 0)
				{
					par = CreateParameter(name, ((ICustomEncrypt)field.Parent).Encrypt(field, (string)value));
				}
				else
				{
					par = CreateParameter(name, value);
				}
			}
			else
			{
				par = CreateParameter(name);
			}

			if (type != null)
				par.DbType = type.Value;
			return par;
		}

		/// <summary>
		/// Create db parameter from IField
		/// </summary>
		/// <param name="field"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(IField field, object value)
		{
			return CreateParameter(field.Name, field, value);
		}

		/// <summary>
		/// Create db parameter from IField
		/// </summary>
		/// <param name="field"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(IField field)
		{
			return field.IsInitialized() ? CreateParameter(field.Name, field, field.GetValue()) : CreateParameter(field.Name);
		}

		/// <summary>
		/// Create db parameter from IField
		/// </summary>
		/// <param name="name"></param>
		/// <param name="field"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(string name, IField field)
		{
			return field.IsInitialized() ? CreateParameter(name, field, field.GetValue()) : CreateParameter(name);
		}

		/// <summary>
		/// Create db parameter
		/// </summary>
		/// <param name="name"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(string name)
		{
			return CreateParameter(name, DBNull.Value);
		}

		/// <summary>
		/// Create db parameter
		/// </summary>
		/// <param name="name"></param>
		/// <param name="type"></param>
		/// <param name="direction"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(string name, DbType type, ParameterDirection direction)
		{
			IDataParameter parameter = CreateParameter(name, type);
			parameter.Direction = direction;
			return parameter;
		}

		#endregion

		/// <summary>
		/// Create db parameter
		/// </summary>
		/// <param name="name"></param>
		/// <param name="value"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(string name, object value)
		{
			IDataParameter parameter;
			if (value == null)
				value = DBNull.Value;
			switch (ProviderType)
			{
				case ProviderType.MSSQL:
					parameter = new SqlParameter(FormatParameterName(ProviderType, name), value);
					if (value == DBNull.Value)
						((SqlParameter)parameter).IsNullable = true;
					break;
				case ProviderType.Access:
					parameter = new OleDbParameter(FormatParameterName(ProviderType, name), value);
					if (value == DBNull.Value)
						((OleDbParameter)parameter).IsNullable = true;
					break;
				case ProviderType.Oracle:
					parameter = new OracleParameter(FormatParameterName(ProviderType, name), value);
					if (value == DBNull.Value)
						((OracleParameter)parameter).IsNullable = true;
					break;
				case ProviderType.MySql:
					throw new NotImplementedException();
				default:
					throw new ArgumentOutOfRangeException();
			}
			return parameter;
		}

		/// <summary>
		/// Create db parameter
		/// </summary>
		/// <param name="name"></param>
		/// <param name="type"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(string name, DbType type)
		{
			IDataParameter parameter;
			switch (ProviderType)
			{
				case ProviderType.MSSQL:
					parameter = new SqlParameter(FormatParameterName(ProviderType, name), type);
					break;
				case ProviderType.Access:
					parameter = new OleDbParameter(FormatParameterName(ProviderType, name), type);
					break;
				case ProviderType.Oracle:
					parameter = new OracleParameter(FormatParameterName(ProviderType, name), type);
					break;
				case ProviderType.MySql:
					throw new NotImplementedException();
				default:
					throw new ArgumentOutOfRangeException();
			}
			parameter.DbType = type;
			return parameter;
		}

		/// <summary>
		/// Create copy of parameter
		/// </summary>
		/// <param name="copyParameter"></param>
		/// <returns></returns>
		public IDataParameter CreateParameter(IDataParameter copyParameter)
		{
			IDataParameter parameter = CreateParameter(copyParameter.ParameterName, copyParameter.Value);
			parameter.DbType = copyParameter.DbType;
			parameter.Direction = copyParameter.Direction;
			parameter.SourceColumn = copyParameter.SourceColumn;
			parameter.SourceVersion = copyParameter.SourceVersion;
			return parameter;
		}

		///<summary>
		/// Format parameter name (specific for provider types)
		///</summary>
		///<param name="providerType"></param>
		///<param name="parameterName"></param>
		///<returns></returns>
		///<exception cref="NotImplementedException"></exception>
		public static string FormatParameterName(ProviderType providerType, string parameterName)
		{
			switch (providerType)
			{
				case ProviderType.MSSQL:
				case ProviderType.Access:
					return parameterName.StartsWith("@") ? parameterName : "@" + parameterName;
				case ProviderType.Oracle:
					if (parameterName.StartsWith("@"))
						parameterName = parameterName.Substring(1);
					return parameterName.StartsWith(":") ? parameterName : ":" + parameterName;
				default:
					throw new NotImplementedException("FormatParameterName - providerType");
			}
		}

		/// <summary>
		/// Create conn
		/// </summary>
		/// <returns></returns>
		protected DbConnection CreateConnection()
		{
			switch (ProviderType)
			{
				case ProviderType.MSSQL:
					return new SqlConnection();
				case ProviderType.Access:
					return new OleDbConnection();
				case ProviderType.Oracle:
					return new OracleConnection();
				default:
					throw new NotImplementedException("CreateConnection - provider type");
			}
		}

		/// <summary>
		/// Create command
		/// </summary>
		/// <returns></returns>
		protected DbCommand CreateCommand()
		{
			switch (ProviderType)
			{
				case ProviderType.MSSQL:
					return new SqlCommand();
				case ProviderType.Access:
					return new OleDbCommand();
				case ProviderType.Oracle:
					return new OracleCommand();
				default:
					throw new NotImplementedException("CreateCommand - provider type");
			}
		}

		/// <summary>
		/// Create data adapter
		/// </summary>
		/// <param name="command"></param>
		/// <returns></returns>
		protected DbDataAdapter CreateDataAdapter(DbCommand command)
		{
			switch (ProviderType)
			{
				case ProviderType.MSSQL:
					return new SqlDataAdapter((SqlCommand)command);
				case ProviderType.Access:
					return new OleDbDataAdapter((OleDbCommand)command);
				case ProviderType.Oracle:
					return new OracleDataAdapter((OracleCommand)command);
				default:
					throw new NotImplementedException("CreateDataAdapter - provider type");
			}
		}

		#endregion

		#region Public Static Functions

		private const string SQL_CONNECTION_MASK = "Data Source={0};Initial Catalog={1};User Id='{2}';Password='{3}';";
		private const string SQL_TRUSTED_CONNECTION_MASK = "Data Source = {0}; Initial Catalog = {1}; Integrated Security = SSPI;";

		private const string ORACLE_CONNECTION_MASK = "Data Source={0};User Id='{1}';Password='{2}';Integrated Security=no";
		private const string ORACLE_TRUSTED_CONNECTION_MASK = "Data Source={0};Integrated Security=SSPI;";

		///<summary>
		/// Format connection string by given parameters
		///</summary>
		///<param name="type"></param>
		///<param name="server"></param>
		///<param name="database"></param>
		///<param name="trusted"></param>
		///<param name="login"></param>
		///<param name="password"></param>
		///<returns></returns>
		///<exception cref="InvalidOperationException"></exception>
		/// <remarks>Currently only Oracle and MSSQL providers supported</remarks>
		public static string FormatConnectionString(ProviderType type, string server, string database, bool trusted, string login, string password)
		{
			switch (type)
			{
				case ProviderType.MSSQL:
					return trusted ? string.Format(SQL_TRUSTED_CONNECTION_MASK, server, database)
										: string.Format(SQL_CONNECTION_MASK, server, database, login, password);
				case ProviderType.Oracle:
					return trusted ? string.Format(ORACLE_TRUSTED_CONNECTION_MASK, server)
										: string.Format(ORACLE_CONNECTION_MASK, server, login, password);
				default:
					throw new InvalidOperationException("Invalid provider type");
			}
		}

		///<summary>
		/// Check if given connection string parameters is valid
		///</summary>
		///<param name="type"></param>
		///<param name="server"></param>
		///<param name="database"></param>
		///<param name="trusted"></param>
		///<param name="login"></param>
		///<param name="password"></param>
		///<returns>true if successed to create and open connection</returns>
		///<exception cref="InvalidOperationException"></exception>
		/// <remarks>Currently only Oracle and MSSQL providers supported</remarks>
		public static bool TestConnection(ProviderType type, string server, string database, bool trusted, string login, string password)
		{
			try
			{
				string connectionString = FormatConnectionString(type, server, database, trusted, login, password);

				// open and close connection
				using (new Connection(type, connectionString))
				{
				}
				return true;
			}
			catch (DbException)
			{
				return false;
			}
		}

		#endregion

	}
}
